﻿-- =============================================
-- Author:		Stocksdale, Jon
-- Create date: JUne 21, 2016
-- Description:	Fetches all External Relationships - THis one using RPC direct call
-- =============================================
CREATE PROCEDURE [dbo].[Update_ExternalRelationships]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DECLARE @EXTVIEW nvarchar(max)
	DECLARE @COMBINEDVIEWS nvarchar(max)
	DECLARE @RELNAME nvarchar(100)
	DECLARE ExternalViewsCursor cursor for
	SELECT sqlraw, ShortRelationshipName FROM
	(SELECT
		SUBSTRING(RafikiNOSQL,CHARINDEX('{',RafikiNOSQL)+1,CHARINDEX('}',RafikiNOSQL)-CHARINDEX('{',RafikiNOSQL)-1)
		as extview,
		ShortRelationshipName
	 from DefinedEntityRelationships
	 ) AS X
	 JOIN DefinedExternalViews
		ON x.ExtView = DefinedExternalViews.ViewName
	
	SET @COMBINEDVIEWS = ''
	Open ExternalViewsCursor
	Fetch NEXT FROM ExternalViewsCursor INTO  @EXTVIEW, @RELNAME
	WHILE @@FETCH_STATUS = 0 BEGIN
		IF (CHARINDEX('ENTITY1ID',@EXTVIEW)>0) BEGIN
				SET @COMBINEDVIEWS = @COMBINEDVIEWS + 'SELECT '''''''' AS SCHOOLID, '''''''' AS PERSONID, CAST(ENTITY1ID AS VARCHAR2(50)) AS ENTITY1ID, CAST(ENTITY2ID AS VARCHAR2(50)) AS ENTITY2ID, '''''+@RELNAME
				+ ''''' AS SHORTRELATIONSHIPNAME, CAST(STARTDATE AS DATE) AS StartDate, CAST(ENDDATE AS DATE) AS EndDate, CAST(NOTES AS VARCHAR2(255)) AS NOTES FROM (' + REPLACE(@EXTVIEW,'''', '''''') + ') UNION '
			END
			ELSE 
			IF (CHARINDEX('PERSONID',@EXTVIEW)>0) BEGIN
				SET @COMBINEDVIEWS = @COMBINEDVIEWS + 'SELECT '''''''' AS SCHOOLID, CAST(PERSONID AS VARCHAR2(30)) AS PERSONID, '''''''' AS ENTITY1ID, CAST(ENTITY2ID AS VARCHAR2(50)) AS ENTITY2ID, '''''+@RELNAME
				+ ''''' AS SHORTRELATIONSHIPNAME,  CAST(STARTDATE AS DATE) AS StartDate, CAST(ENDDATE AS DATE) AS EndDate, CAST(NOTES AS VARCHAR2(255)) AS NOTES FROM (' + REPLACE(@EXTVIEW,'''', '''''') + ') UNION '
			END
			ELSE 
			IF (CHARINDEX('SCHOOLID',@EXTVIEW)>0) BEGIN
				SET @COMBINEDVIEWS = @COMBINEDVIEWS + 'SELECT CAST(SCHOOLID AS VARCHAR2(30)) AS SCHOOLID, '''''''' AS PERSONID, '''''''' AS ENTITY1ID, CAST(ENTITY2ID AS VARCHAR2(50)) AS ENTITY2ID, '''''+@RELNAME
				+ ''''' AS SHORTRELATIONSHIPNAME,  CAST(STARTDATE AS DATE) AS StartDate, CAST(ENDDATE AS DATE) AS EndDate, CAST(NOTES AS VARCHAR2(255)) AS NOTES FROM (' + REPLACE(@EXTVIEW,'''', '''''') + ') UNION '
			END
			Fetch NEXT FROM ExternalViewsCursor INTO  @EXTVIEW, @RELNAME
	END
	SET @COMBINEDVIEWS = SUBSTRING(@COMBINEDVIEWS,0,LEN(@COMBINEDVIEWS)-6) + ')'
	CLose ExternalViewsCursor
	DEALLOCATE ExternalViewsCursor
	SELECT @COMBINEDVIEWS
	CREATE TABLE #Temp (
		SCHOOLID nvarchar(30),
		PERSONID nvarchar(30),
		ENTITY1ID nvarchar(100),
		ENTITY2ID nvarchar(100),
		SHORTRELATIONSHIPNAME nvarchar(20),
		STARTDATE date,
		ENDDATE date,
		NOTES nvarchar(255)
	 )
	 INSERT INTO #Temp
		EXEC('EXEC('''+ @COMBINEDVIEWS+''') AT PSPRODSERVER')

	 DELETE FROM ExternalEntityRelationships
	 INSERT INTO ExternalEntityRelationships
	 SELECT  COALESCE(CAST(x.Entity1ID AS nvarchar), CAST(ab.EntityID AS nvarchar), CAST(aa.EntityID AS nvarchar)) as Entity1ID, Entity2ID, SHORTRELATIONSHIPNAME, STARTDATE, ENDDATE, NOTES FROM
	  #Temp x LEFT JOIN (SELECT Attributes.* FROM Attributes JOIN DefinedAttributes da ON Attributes.AttributeID=da.AttributeID WHERE da.AttributeName='PersonID') ab ON CAST(x.PERSONID AS nvarchar)=CAST(ab.AttributeValueString AS nvarchar)
		LEFT JOIN (SELECT Attributes.* FROM Attributes JOIN DefinedAttributes da2 ON Attributes.AttributeID=da2.AttributeID WHERE da2.AttributeName='SchoolID') aa ON CAST(x.SCHOOLID  AS nvarchar)=CAST(aa.AttributeValueString AS nvarchar)
	 DROP TABLE #Temp


END